package cn.itcast.web.controller.cargo;

import cn.itcast.service.cargo.ContractProductService;
import cn.itcast.vo.ContractProductVo;
import cn.itcast.web.controller.BaseController;
import com.alibaba.dubbo.config.annotation.Reference;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.ServletOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * 出货表的POI导出
 */
@Controller
@RequestMapping("/cargo/contract")
public class OutProductController extends BaseController {

    @Reference
    private ContractProductService contractProductService;

    /**
     * 出货表的POI导出, 进入导出页面
     */
    @RequestMapping("/print")
    public String print(){
        return "cargo/print/contract-print";
    }

    /**
     * 出货表的POI导出 (1) 普通导出，使用XSSF导出excel2007
     * 第一：导出第一行
     * 第二：导出第二行
     * 第三：导出数据库（调用servic
     * 第四：导出下载
     */
    @RequestMapping("/printExcel_11")
    public void printExcel_11(String inputDate) throws IOException { // 2019-06
        // 第一：导出第一行
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("出货表导出");
        // 合并单元格：开始行，结束行，开始列，结束列（0018）
        sheet.addMergedRegion(new CellRangeAddress(0,0,1,8));
        // 设置单元格宽度
        sheet.setColumnWidth(0,256*6);
        sheet.setColumnWidth(1,256*26);
        sheet.setColumnWidth(2,256*11);
        sheet.setColumnWidth(3,256*29);
        sheet.setColumnWidth(4,256*15);
        sheet.setColumnWidth(5,256*12);
        sheet.setColumnWidth(6,256*10);
        sheet.setColumnWidth(7,256*10);
        sheet.setColumnWidth(8,256*10);

        Row row = sheet.createRow(0);
        row.setHeightInPoints(36);
        // 创建第一行的第二列，合并单元格.  2019-06-->2019年6月份出货表
        Cell cell = row.createCell(1);
        String title = inputDate.replace("-0","-").replace("-","年") + "月份出货表";
        // 设置单元格内容
        cell.setCellValue(title);
        // 设置样式
        cell.setCellStyle(this.bigTitle(workbook));

        // 第二：导出第二行
        String[] titles = {"客户","订单号","货号","数量","工厂","工厂交期","船期","贸易条款"};
        row = sheet.createRow(1);
        row.setHeightInPoints(26);
        for (int i = 0; i < titles.length; i++) {
            cell = row.createCell(i+1);
            cell.setCellValue(titles[i]);
            // 设置样式
            cell.setCellStyle(this.title(workbook));
        }

        // 第三：导出数据库（调用service）
        List<ContractProductVo> list =
                contractProductService.findByShipTime(getLoginCompanyId(), inputDate + "%");
        if (list != null && list.size()>0){
            int index = 2;
            for (ContractProductVo vo : list) {
                // 创建每一个数据行，从第三行开始
                row = sheet.createRow(index++);

                cell = row.createCell(1);
                cell.setCellValue(vo.getCustomName());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(2);
                cell.setCellValue(vo.getContractNo());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(3);
                cell.setCellValue(vo.getProductNo());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(4);
                cell.setCellValue(vo.getCnumber());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(5);
                cell.setCellValue(vo.getFactoryName());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(6);
                cell.setCellValue(vo.getDeliveryPeriod());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(7);
                cell.setCellValue(vo.getShipTime());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(8);
                cell.setCellValue(vo.getTradeTerms());
                cell.setCellStyle(this.text(workbook));
            }
        }

        // 第四：导出下载
        response.setCharacterEncoding("UTF-8");
        // 设置下载响应头（浏览器会以下载方式处理响应结果）
        response.setHeader("content-disposition","attachment;filename=export.xlsx");
        ServletOutputStream out = response.getOutputStream();
        // 把excel文件流，写入到response输出流，实现下载
        workbook.write(out);
        workbook.close();
        out.close();
    }


    /**
     * 出货表的POI导出 (2) 模板导出：先读取一个有样式的excel模板，再往里面填充数据
     */
    @RequestMapping("/printExcel_22")
    public void printExcel_22(String inputDate) throws IOException {
        //【获取excel模板,文件流】
        InputStream in = request.getSession()
                .getServletContext()
                .getResourceAsStream("/make/xlsprint/tOUTPRODUCT.xlsx");

        // 第一：导出第一行
        Workbook workbook = new XSSFWorkbook(in);
        // 【获取工作表】
        Sheet sheet = workbook.getSheetAt(0);

        //【获取第一行、第一行的第二列】
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(1);
        String title = inputDate.replace("-0","-").replace("-","年") + "月份出货表";
        // 设置单元格内容
        cell.setCellValue(title);

        // 【获取第三行样式】
        CellStyle[] cellStyles = new CellStyle[8];
        row = sheet.getRow(2);
        for (int i = 0; i < cellStyles.length; i++) {
            cellStyles[i] = row.getCell(i+1).getCellStyle();
        }

        // 第三：导出数据库（调用service）
        List<ContractProductVo> list =
                contractProductService.findByShipTime(getLoginCompanyId(), inputDate + "%");
        if (list != null && list.size()>0){
            int index = 2;
            for (ContractProductVo vo : list) {
                // 创建每一个数据行，从第三行开始
                row = sheet.createRow(index++);

                cell = row.createCell(1);
                cell.setCellValue(vo.getCustomName());
                cell.setCellStyle(cellStyles[0]);

                cell = row.createCell(2);
                cell.setCellValue(vo.getContractNo());
                cell.setCellStyle(cellStyles[1]);

                cell = row.createCell(3);
                cell.setCellValue(vo.getProductNo());
                cell.setCellStyle(cellStyles[2]);

                cell = row.createCell(4);
                cell.setCellValue(vo.getCnumber());
                cell.setCellStyle(cellStyles[3]);

                cell = row.createCell(5);
                cell.setCellValue(vo.getFactoryName());
                cell.setCellStyle(cellStyles[4]);

                cell = row.createCell(6);
                cell.setCellValue(vo.getDeliveryPeriod());
                cell.setCellStyle(cellStyles[5]);

                cell = row.createCell(7);
                cell.setCellValue(vo.getShipTime());
                cell.setCellStyle(cellStyles[6]);

                cell = row.createCell(8);
                cell.setCellValue(vo.getTradeTerms());
                cell.setCellStyle(cellStyles[7]);
            }
        }

        // 第四：导出下载
        response.setCharacterEncoding("UTF-8");
        // 设置下载响应头（浏览器会以下载方式处理响应结果）
        response.setHeader("content-disposition","attachment;filename=export.xlsx");
        ServletOutputStream out = response.getOutputStream();
        // 把excel文件流，写入到response输出流，实现下载
        workbook.write(out);
        workbook.close();
        out.close();
    }



    /**
     * 出货表的POI导出 (3) SXSSF 实现百万数据poi导出，可以避免内存溢出
     */
    @RequestMapping("/printExcel")
    public void printExcel(String inputDate) throws Exception {
        // 只需要把XSSF改为SXSSF
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("出货表导出");
        // 合并单元格：开始行，结束行，开始列，结束列（0018）
        sheet.addMergedRegion(new CellRangeAddress(0,0,1,8));
        // 设置单元格宽度
        sheet.setColumnWidth(0,256*6);
        sheet.setColumnWidth(1,256*26);
        sheet.setColumnWidth(2,256*11);
        sheet.setColumnWidth(3,256*29);
        sheet.setColumnWidth(4,256*15);
        sheet.setColumnWidth(5,256*12);
        sheet.setColumnWidth(6,256*10);
        sheet.setColumnWidth(7,256*10);
        sheet.setColumnWidth(8,256*10);

        Row row = sheet.createRow(0);
        row.setHeightInPoints(36);
        // 创建第一行的第二列，合并单元格.  2019-06-->2019年6月份出货表
        Cell cell = row.createCell(1);
        String title = inputDate.replace("-0","-").replace("-","年") + "月份出货表";
        // 设置单元格内容
        cell.setCellValue(title);
        // 设置样式
        cell.setCellStyle(this.bigTitle(workbook));

        // 第二：导出第二行
        String[] titles = {"客户","订单号","货号","数量","工厂","工厂交期","船期","贸易条款"};
        row = sheet.createRow(1);
        row.setHeightInPoints(26);
        for (int i = 0; i < titles.length; i++) {
            cell = row.createCell(i+1);
            cell.setCellValue(titles[i]);
            // 设置样式
            cell.setCellStyle(this.title(workbook));
        }

        // 第三：导出数据库（调用service）
        List<ContractProductVo> list =
                contractProductService.findByShipTime(getLoginCompanyId(), inputDate + "%");
        if (list != null && list.size()>0){
            int index = 2;
            for (ContractProductVo vo : list) {
                for (int i = 1; i < 30000; i++) {

                    // 创建每一个数据行，从第三行开始
                    row = sheet.createRow(index++);

                    cell = row.createCell(1);
                    cell.setCellValue(vo.getCustomName());

                    cell = row.createCell(2);
                    cell.setCellValue(vo.getContractNo());

                    cell = row.createCell(3);
                    cell.setCellValue(vo.getProductNo());

                    cell = row.createCell(4);
                    cell.setCellValue(vo.getCnumber());

                    cell = row.createCell(5);
                    cell.setCellValue(vo.getFactoryName());

                    cell = row.createCell(6);
                    cell.setCellValue(vo.getDeliveryPeriod());

                    cell = row.createCell(7);
                    cell.setCellValue(vo.getShipTime());

                    cell = row.createCell(8);
                    cell.setCellValue(vo.getTradeTerms());
                }
            }
        }

        // 第四：导出下载
        response.setCharacterEncoding("UTF-8");
        // 设置下载响应头（浏览器会以下载方式处理响应结果）
        response.setHeader("content-disposition","attachment;filename=export.xlsx");
        ServletOutputStream out = response.getOutputStream();
        // 把excel文件流，写入到response输出流，实现下载
        workbook.write(out);
        workbook.close();
        out.close();
    }



    //大标题的样式
    public CellStyle bigTitle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short)16);
        font.setBold(true);//字体加粗
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);				//横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
        return style;
    }

    //小标题的样式
    public CellStyle title(Workbook wb){
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short)12);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);				//横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
        style.setBorderTop(BorderStyle.THIN);						//上细线
        style.setBorderBottom(BorderStyle.THIN);					//下细线
        style.setBorderLeft(BorderStyle.THIN);						//左细线
        style.setBorderRight(BorderStyle.THIN);						//右细线
        return style;
    }

    //文字样式
    public CellStyle text(Workbook wb){
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("Times New Roman");
        font.setFontHeightInPoints((short)10);

        style.setFont(font);

        style.setAlignment(HorizontalAlignment.LEFT);				//横向居左
        style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
        style.setBorderTop(BorderStyle.THIN);						//上细线
        style.setBorderBottom(BorderStyle.THIN);					//下细线
        style.setBorderLeft(BorderStyle.THIN);						//左细线
        style.setBorderRight(BorderStyle.THIN);						//右细线

        return style;
    }
}
